一、概述

Oracle数据库的概念和其它数据库不一样，数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库。

• Oracle通过数据库实例来加载和管理数据库，每个运行的Oracle数据库都对应一个Oracle实例(Instance)，也可以称为例程。 当数据库服务器上的一个数据库启动时，Oracle将为其分配一块内存区间，叫做系统全局区(SGA)，然后启动多个进程。 SGA和Oracle进程结合在一起，就是一个Oracle实例。 为了区分不同的实例，每个Oracle实例都有一个系统标识符SID，通常SID与数据库同名。

• Oracle数据库实例的状态

  • 打开 :启动实例，装载并打开数据库。该模式是默认的启动模式，它允许任何有效用户连接到数据库，并执行数据访问操作。

• 关闭: 将Oracle实例从允许用户访问数据的状态转换为不可使用状态。

  • 已装载:启动实例并装载数据库，但不打开数据库。该模式用于数据库的数据文件的恢复操作等。不允许用户访问数据库数据

• 已启动:启动实例，但不装载数据库。该模式用于对控制文件进行管理或重新创建数据库等，不允许用户访问数据库数据

• 数据文件

  数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件 组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行

• 表空间

  表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为 system 表空间)。 每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。

  注：表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。

  由于 oracle 的数据库不是普通的概念，oracle 是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！

• 用户

  用户是在表空间下建立的。用户登陆后只能看到和操作自己的表, ORACLE的用户与 MYSQL 的数据库类似，每建立一个应用需要创建一个用户

1.1 授权

权限

系统权限：系统规定用户使用数据库的权限。（系统权限是对用户而言)。

实体权限：某种权限用户对其它用户的表或视图的存取权限。（是针对表或视图而言的）。

实体权限分类：select, update, insert, alter, index, delete, all //all 包括所有权限 execute //执行存储过程权限

用户

• SYS用户

  SYS用户是Oracle中的超级用户，主要用于维护系统信息和管理实例，数据库中数据字典的所有表和视图都存储在SYS模式中

• SYSTEM用户

  SYSTEM用户是Oracle中默认的管理员，它拥有DBA权限。该用户拥有Oracle管理工具使用的内部表和视图，通常通过SYSTEM用户管理Oracle数据库的用户、权限和存储等 。

• SCOTT用户

  SCOTT是Oracle数据库的一个示范账号。SCOTT用户模式包含4张示范表，SCOTT用户的默认口令为tiger。

  解锁：

  -- 解锁scott用户
  alter user scott account unlock;
  -- 连接scott
  conn scott/tiger；
  -- 连接sys
  conn sys/1234 as sysdba；
  
  -- 创建用户 dentified by 后边是用户的密码   default tablespace 后边是表空间名称   
  -- oracle数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。
  create user itcastuser
  identified by itcast 
  default tablespace itcast
  
  -- 为角色授权
  grant dba to itcastuser
  -- 授予实体权限
  grant select, update, insert on product to user02; 
  -- 授权给所有用户
  grant all on product to public; 
  
  -- 查询权限
  -- 用户可以查询的表
   select owner, table_name from all_tables; 
   -- 用户创建的表
   select table_name from user_tables; 
   -- 获取权限可以存取的表
   select grantor, table_schema, table_name, privilege from all_tab_privs;
   -- 授出权限的表
   select grantee, owner, table_name, privilege from user_tab_privs;
  -- 查询用户拥有哪些系统权力
  select grantee，privilege from dba_sys_privs where grantee=‘SCOTT’;
  -- 查看用户拥有哪些对象权利
  select grantee，privilege，owner，table_name from dba_tab_privs where grantee=‘SCOTT’;
  
  -- dba用户可以操作所有用户的任意基表，无需授权
  
  -- 实体权限传递
   grant select, update on product to user02 with grant option;
   -- 实体权限回收
   Revoke select, update on product from user02; 
  -- 查询用户权限
  select * from dba_role_privs;
  
  -- 删除用户
  drop user 用户名 cascade;--加上 cascade 则将用户连同其创建的东西全部删除

1）如果取消某个用户的对象权限，那么对于这个用户使用 WITH GRANT OPTION 授予权限的

用户来说，同样还会取消这些用户的相同权限，也就是说取消授权时级联的。

角色

ORACLE 数据库系统预先定义了 CONNECT 、RESOURCE、 DBA、 EXP_FULL_DATABASE、 IMP_FULL_DATABASE 五个角色。

CONNECT 具有创建表、视图、序列等特权；(alter session create cluster )

RESOURCE 具有创建过程、触发器、表、序列等特权、

DBA 具有全部系统特权；

EXP_FULL_DATABASE、 IMP_FULL_DATABASE 具有卸出与装入数据库的特权。

Oracle是根据角色进行授权，已存在三个重要的角色：connect角色，resource角色，dba角色

CONNECT角色	是授予最终用户的典型权利，最基本的 - ALTER SESSION --修改会话 - CREATE CLUSTER --建立聚簇 - CREATE DATABASE LINK --建立数据库链接 - CREATE SEQUENCE --建立序列 - CREATE SESSION --建立会话 - CREATE SYNONYM --建立同义词 - CREATE VIEW --建立视图 - RESOURCE角色： --是授予开发人员的 - CREATE CLUSTER --建立聚簇 - CREATE PROCEDURE --建立过程 - CREATE SEQUENCE --建立序列 - CREATE TABLE --建表 - CREATE TRIGGER --建立触发器 - CREATE TYPE --建立类型
DBA角色	拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构，并且系统权限也需要DBA授出，且DBA用户可以操作全体用户的任意基表，包括删除
resource角色	只可以创建实体，不可以创建数据库结构。

对于普通用户：授予 connect, resource 权限。

对于 DBA 管理用户：授予 connect，resource, dba 权限。

-- 创建角色
create role role1; 

-- 授权
grant create any table,create procedure to role1;


-- 给用户授予角色
grant role1 to user1; 
grant role1 to user1 with admin option;

-- 查看系统中所有角色
select * from dba_roles; 
-- 查看用户被授予的角色
select grantee，granted_role from dba_role_privs where grantee=‘SCOTT’;
-- 查看角色中包含的系统权限
select role，privilege from role_sys_privs where role=‘ROLE1’; 
-- 查看角色中包含的对象权限
select role，privilege，from role_tab_privs where role=‘ROLE1’;
-- 将角色授予角色
grant role1 to role2; 
-- 查看角色所包含权限
select * from role_sys_privs; 
-- 创建带有口令以角色(在生效带有口令的角色时必须提供口令) 
create role role1 identified by password1; 
-- 修改角色，是否需要口令
alter role role1 not identified; 
alter role role1 identified by password1; 
-- 设置当前用户要生效的角色
-- 使 role1,role2 生效 
set role role,role2;
-- 使用带有口令的 role1 生效
set role role1 identified by password1;
-- 使用该用户的所有角色生效
set role all;
-- 设置所有角色失效
set role none;
-- 除 role1 外的该用户的所有其它角色生效。
set role all except role1;
-- 查看当前用户的生效的角色
select * from SESSION_ROLES;

-- 修改指定用户，设置其默认角色
alter user user1 default role role1;
alter user user1 default role all except role1; 

-- 删除角色
-- 角色删除后，原来拥用该角色的用户就不再拥有该角色了，相应的权限也就没有了。
-- 1)无法使用 WITH GRANT OPTION 为角色授予对象权限 
-- 2)可以使用 WITH ADMIN OPTION 为角色授予系统权限,取消时不是级联 
drop role role1;

-- 查看角色中还包含哪些角色
select role，granted_role from role_role_privs where role=‘DBA’；

--授予用户 DBA、RESOURCE 这俩个角色后系统会自动再授予用户 unlimited tablespace

注意：

对于日常的管理任务，建议使用SYSTEM用户登录Oracle数据库服务器。 如果需要执行备份、恢复、更改数据库的任务，就必须以SYS用户登录Oracle数据库服务器

1.2 常用数据类型

char	CHAR表示固定长度字符串，长度不够的用空格补充，最多可以存储2000字节 CHAR类型区分中英文，中文在CHAR中占两个字节，而英文只占一个字节
VARCHAR2	VARCHAR2表示可变长度字符串，最多可以存储4000字节 在定义该数据类型时，应该指定其大小。与CHAR类型相比，使用VARCHAR2可以节省磁盘空间
NUMBER	NUMBER类型可以存储正数、负数、零、定点数和精度为38位的浮点数 NUMBER(M,N)。其中，M表示精度，代表数字的总位数；N表示小数点右边数字的位数
DATE	用于存储表中的日期和时间数据，取值范围是公元前4712年1月1日至公元9999年12月31日 长度是7，7个字节分别表示世纪、年、月、日、时、分和秒
TIMESTAMP	用于存储日期的年、月、日以及时间的小时、分和秒值 其中，秒值精确到小数点后6位，该数据类型同时包含时区信息
CLOB	CLOB（Character Large Object）数据类型用于存储可变长度的字符数据，最多可存储4GB数据 用于存储VARCHAR2类型不能存储的长文本信息
BLOB	BLOB（Binary Large Object）数据类型用于存储较大的二进制对象，如图形、视频剪辑和声音剪辑等，最多可以存储4GB数据

1.3 常用函数

一般不将处理数据的番薯放到字段上，会导致每个字段都转换，效率低。

• 字符函数

  -- concat 字符连接函数 还可以使用|| 字符串连接符
  select concat('hello',' world')|| '!!!!'  from dual;
  
  -- initcap 首字符大写 (参数无论是什么样字母组成，都将第一个字母大写，其余小写)
  select initcap('hello') as name from dual;
  
  -- instr 字符串查找 参数1：被查找的字符串，参数2：要查找的字符串参数3：查找的起始位置参数4：第几次出现
  select instr('hello','ll',1,1) instring from dual;
  
  -- initcap首字母大写其余小写，upper 大写 lower 小写 
  select initcap(ename),upper(ename),lower(ename),eanme   from emp;
  
  -- lpad , rpad 在字符左右补充函数l 左 r 右 参数1：原字符串参数2：补充后达到的个数参数3：补充的字符
  select rpad('hello',10,'#') from dual;
  select lpad(rpad('hello',10,'#'),15,'*') from dual;
  
  -- ltrim,rtrim字符删除函数 ltrim('字符串1','字符2') ：从字符串1左边删去指定的字符2，如果第二个参数省略，则删除字符串1左边的空格。
  -- LTRIM	删除左边出现的字符串  RTRIM	删除右边出现的字符串
  select rtrim('hello   ') from dual;
  select rtrim('hello    ',' ') from dual;
  select ltrim( rtrim('*hello    ',' '),'*') from dual;
  
  --substr 取子字符串 参数1，被截取的字符串 参数2，索引开始的位置 参数3，截取的个数  
  select substr(‘hello world’,6,5) from  dual;
  
  -- replace 字符串替换 参数1，需要进行替换操作的字符串 参数2，字符串中需要进行替换的内容 参数3，替换为的字符
  select replace('hello world','hello','hi') from dual;
  
  -- trim 字符删除
  -- trim(type '字符1'  from  '字符串2')：分别从字符串2的两边开始删除指定的字符1，如果第前两个参数和from省略，则删除字符串2两边的空格；
  -- type:删除的方式（leading：从左边开始删除；trailing：从右边开始删除；both：默认方式，从两边删除）
  select trim('-' from '-------hello------') from dual;
  select trim(leading '0' from '000123')  from dual;
  
  -- length返回字符串的长度
  -- chr参数为整数,表示某个字符的Unicode码,返回对应的字符
  -- ascll 返回与指定的字符对应的十进制数

• 数学函数

  -- ceil 向上取整
  select  ceil(3.1415927)  from dual; 
  
  -- floor 向下取整
  select floor(3.1415927) from dual
  
  -- round四舍五入函数 trunc截断函数  第二个参数为正时，表示从小数点向后计算。第二个参数为负时，表示从小数点向前计算。
  -- 对日期也起作用
  select round(124.1666,-2),trunc(124.1666,2) from dual;
  
  
  -- abs 绝对值 返回指定值的绝对值
  
  --mod取余 mod（n1,n2）返回n1除以n2的余数
  
  -- power次方 power(n1,n2)返回n1的n2次方
  
  -- sign 判断正负 取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
  
  -- sqrt取根 返回数字的根

• 日期函数

  -- 模板关键字
  -- HH	一天的小时数 （01-12） HH12	一天的小时数 （01-12） HH24	一天的小时数 （00-23）
  -- MI  分钟 （00-59） SS  秒 （00-59） MS  毫秒 (000-999)  US	微秒 (000000-999999)  SSSS	午夜后的秒 （0-86399）
  -- AM 或 A.M. 或 PM 或 P.M.	正午标识（大写）  am 或 a.m. 或 pm 或 p.m.	正午标识（小写）
  -- Y,YYY	带逗号的年（4 和更多位）  YYYY	年（4和更多位）  YYY	年的后三位  YY	年的后两位  Y	年的最后一位
  
  -- sysdata  用来得到系统的当前日期
  select sysdate, to_char(sysdate,’dd-mm-yyyy day’) from dual;
  
  -- add_months 增加或减去月份
  select to_char( add_months(sysdate,2),'yyyy-mm') from dual;
  
  -- last_day 返回日期的最后一天
  
  -- months_between(date2,data1) 返回date2和date1相差的月份
  select months_between('9-12月-1999'，'19-3月-1999') 相差月份 from dual; 
  
  -- next_day(date,'day') 返回日期date和星期x之后计算下一个星期的日期
  
  -- trunc(date,fmt) 按照给出的要求将日期截断,如果fmt=‘mi’表示保留分,截断秒
  select to_char(  trunc(sysdate,’hh’) ,   ’yyyy.mm.dd hh24:mi:ss’ ) hh, to_char(  trunc(sysdate,’mi’) ,   ’yyyy.mm.dd hh24:mi:ss’ ) hm from dual;
  
  -- systimestamp 查看当前系统默认精度的日期时间和更高精度的时间戳
  SELECT SYSDATE,Systimestamp FROM dual;

• 转换函数

  日期格式的常见元素：

  

  数字格式的常见元素：

  

  提示：9代表任意数字，可以不存在。0代表数字，如果该位置不存在，则用0占位。

  -- 隐式转换 oracle可以自动完成三种类型的转换 隐式转换需满足是可以被转换的
  -- varchar2 或 char 转换为 Number数字
  -- varchar2或char 转换为 DATE 日期
  -- Number 数字  转换为 varchar2 字符
  -- DATE 日志  转换为 varchar2 字符
  
  -- to_char(timestamp, text) 将日期按特定格式显示 把 timestamp 转换成 string
  select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual
  
  
  --to_char(int, text) 把 int4/int8 转换成 string
  -- to_char(double precision, text)把 real/double precision 转换成 string
  -- to_char(numeric, text) 把 numeric 转换成 string
  
  -- to_date(text, text) 将字符串数据转换成为日期类型 把 string 转换成 date
  select * from emp where hiredate> to_date('1985-12-2','yyyy-mm-dd');
  
  -- to_timestamp(text, text) 把 string  转换成 timestamp
  -- to_number(text, text) 把 string 转换成 numeric
  select  to_number('1999') + 10 from dual;
  
  -- CHARTOROWID 将 字符转换到 rowid 类型 
  --CONVERT 转换一个字符节到另外一个字符节 
  --HEXTORAW 转换十六进制到 raw 类型
  -- RAWTOHEX 转换 raw 到十六进制 
  --ROWIDTOCHAR 转换 ROWID 到字符 
  --TO_CHAR 转换日期格式到字符串 
  --TO_DATE 按照指定的格式将字符串转换到日期型 
  --TO_MULTIBYTE 把单字节字符转换到多字节 
  --TO_NUMBER 将数字字串转换到数字 
  --TO_SINGLE_BYTE 转换多字节到单字节

• 其他函数

  -- uid 返回标识当前用户的唯一整数
  
  -- user 返回当前用户的名字
  
  --nvl(expr1, expr2)  NVL(expr1, expr2)表示如果expr1为NULL，返回expr2；不为NULL，返回expr1。注意expr1和expr2两者的类型要一致
  select ename,hiredate,sal,comm,nvl(comm,0),sal+nvl(comm,0) from emp;
  
  -- nvl(expr1, expr2, xpr3) expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型
  select comm,nvl2(comm,comm,0) from emp;
  
  -- nullif (expr1, expr2) expr1和expr2相等返回NULL，不等返回expr1
  -- coalesce(a,b,c,d)，从左往右查找，当找到第一个不为null的值的时候，就显示这第一个有值的值
  
  -- 类似if....else if...esle
  -- Col/expression:列名或表达式
  -- Search1，search2...:用于比较的条件
  -- Result1, result2...:返回值
  -- 如果col/expression和Searchi匹配就返回resulti,否则返回default的默认值
  -- decode (字段名,要翻译的原始值1，翻译后的值1，......，其他不满足翻译条件的默认值)
  DECODE(col/expression, [search1,result1],[search2, result2]...[default])
  
  -- case when
  --case 字段 when 要翻译的值 then 翻译的结果
  --when 要翻译的值 then 翻译的结果
  --		......
  --		else 默认的结果值
  --end
  CASE expr WHEN comparison_expr1 THEN return_expr1
  	[WHEN comparison_expr2 THEN return_expr2
  	WHEN comparison_exprn THEN return_exprn
  	ELSE else_expr]
  END
  
  
  -- RANK 相同的值排名相同，排名跳跃
  -- 对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名跳跃
  select rank() over(order by usenum desc ),usenum from T_ACCOUNT
  
  -- DENSE_RANK 相同的值排名相同，排名连续
  --对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名连 续
  select dense_rank() over(order by usenum desc ),usenum from T_ACCOUNT
  
  -- ROW_NUMBER 返回连续的排名，无论值是否相等
  -- 对 T_ACCOUNT 表的 usenum 字段进行排序，返回连续的排名，无论值是 否相等
  select row_number() over(order by usenum desc ),usenum from T_ACCOUNT

• 聚合函数

  参考数据库相关中的聚合函数

  count(), max(), min(), sum(), avg()多行函数会自动滤空。

  distinct可用来过滤掉多余的重复记录只保留一条，但往往只用 它来返回不重复记录的条数，而不是用它来返回显示不重记录的所有值。因此，一般和count配合使用，作为统计非空且不重复的记录数。

  DISTINCT关键字效率会比较低，如果仅仅是为了显示不重复的记录，建议使用group by；

  慢的原因是：

  distinct只有用二重循环查询来解决，而这样对于一个数据量非常大的表来说，无疑是会直接影响到效率的。

  [了解]：MAX()和MIN()函数不仅可以作用于数值型数据，也可以作用于字符串或是日期时间数据类型的数据。

• 集合运算

  --UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。 
  --UNION(并集)，返回各个查询的所有记录，不包括重复记录。 
  --INTERSECT(交集)，返回两个查询共有的记录。 
  --MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之 后剩余的记录。

1.4 表空间

表空间是Oracle对物理数据库上相关数据文件（ORA或者DBF文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为system表空间)。

每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。

创建表空间

-- itcast 为表空间名称
-- datafile 指定表空间对应的数据文件
-- size 后定义的是表空间的初始大小
-- autoextend on 自动增长 ，当表空间存储都占满时，自动增长
-- next 后指定的是一次自动增长的大小。

create tablespace itcast
datafile 'c:\itcast.dbf' 
size 100m 
autoextend on 
next 10m

1.5 数据文件（dbf、ora）

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

注： 表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。

由于oracle的数据库不是普通的概念，oracle是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！

二、基本使用

2.1 实例管理命令

#关闭Oracle实例 ：
#正常关闭数据库实例
#提交shutdown normal命令后，Oracle数据库不再接受新的连接。
#数据库会一直等待当前数据库的用户都断开连接后，再关闭数据库实例。因此通常此种关闭方式需要等待很长的时间。一般不采用此种方式关闭数据库实例。
shutdown normal

#立即关闭数据库实例
#数据库不再接受新的连接，也不允许开始新的事务，所有未提交的事务都将被撤销。
#数据库不会等待所有在线用户断开连接，只要事务撤销完毕，就立即关闭数据库。
shutdown immediate

#以事务处理方式关闭数据库实例
#数据库不允许新的连接，也不允许开始新的事务，但是会等待已有事务提交后才关闭数据库。
#数据库不会等待所有在线用户断开连接，只要事务提交完毕，就立即关闭数据库。
shutdown transactional

#强制关闭数据库实例
#数据库不再接受新的连接，也不允许开始新的事务，所有未提交的事务都将被终止。
#立即切断所有在线用户连接。
shutdown abort

# Oracle实例管理


<NolebasePageProperties />




# 用户必须以sysdba 身份登录
conn sys/admin as sysdba
#关闭实例 ：
 shutdown immediate 
# 启动实例，但不装载数据库：
 startup nomount 
# 启动实例，并装载数据库：
 startup mount 
# 启动数据库实例，并装载数据库：
 startup open	#open参数可以省略

2.2 数据导入导出

整库导入导出

-- 添加参数 full=y 就是整库导出 
--  file=文件名 不添加默认为 EXPDAT.DMP
-- 执行命令后会在当前目录下生成一个叫 EXPDAT.DMP，此文件为备份文件。
exp system/itcast file=文件名 full=y

-- 整库导入
-- 不指定file参数时，默认使用备份文件 EXPDAT.DMP 进行导入
imp system/itcast full=y file=water.dmp

按用户导入导出

-- 按用户导出
exp system/itcast owner=wateruser file=wateruser.dmp

-- 按用户导入
imp system/itcast file=wateruser.dmp fromuser=wateruser

按表导入导出

-- 按表导出 tables 参数指定需要导出的表，如果有多个表用逗号分割即可
exp wateruser/itcast file=a.dmp tables=t_account,a_area

-- 按表导入
imp wateruser/itcast file=a.dmp tables=t_account,a_area

三、核心知识

3.1 序列

概述：

• 序列（SEQUENCE）是一个命名的顺序编号生成器，它能以串行的方式生成一系列顺序整数
• 序列的主要用途 主键、外键值应用需求 流水号应用需求 序列的生成与定义的内容

• 序列的创建及使用

  -- 创建序列
  CREATE SEQUENCE sequnce_name
   CREATE SEQUENCE   --序列名
  [START WITH n1]	--指定要生成的第一个序列号 
  [INCREMENT BY n2]  --用于指定序列号之间的间隔，默认值为1 
  [{MAXVALUE n3 | NOMAXVALUE}]  --指定序列可以生成的最大值 
  [{MINVALUE n4 | NOMINVALUE}]  --指定序列可以生成的最小值 
  [{CACHE n5 | NOCACHE}]  --用于指定在高速缓存中可以预分配的序列号个数，默认为20 
  [{CYCLE | NOCYCLE}]  --用于指定在达到序列的最大值或最小值后是否循环  循环的序列，第一次循环是从开始值开始循环，而第二次循环是从最小值开始循环。创建循环的序列必须指定最大值。
  [ORDER];  --用于指定按顺序生成序列号 ，确保序列唯一和有序 
  --需要在自己的方案中创建序列时，用户必须具有CREATE SEQUENCE系统权限 
  
  -- 使用
  -- NEXTVAL列返回序列生成的下一个值
  -- CURRVAL列返回序列生成的当前值
  -- 在第一次引用CURRVAL列之前，必须引用过一次NEXTVAL列，用于初始化序列的值，否则会出现错误提示
  --select 序列名.NEXTVAL from dual;
  select 序列名.CURRVAL from dual;
  --示例
  insert into tab_test values(seq_test.nextval,'张三');
  
  -- 修改序列
  -- 使用 ALTER SEQUENCE 语句修改序列，不能更改序列的 START WITH 参数
  ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE;
  -- 删除序列
  DROP SEQUENCE 序列名称;

3.2 dual表

概述：

• dual表是Oracle数据库中的一个虚表，它有一行一列，所有者是SYS用户，但可以被数据库中的所有用户使用

• 不能向该表插入数据，但可以使用该表来选择系统变量，或者求一个表达式的值

  • select 3+4 from dual;

3.3 分页查询

• Rownum

  --是一个伪列，它会根据返回的记录生成一个序列化的数字。可以使用ROWNUM列返回查询结果集中前N条记录
  select rownum，列名 ，列名 from 表名 where rownum <10
  --分页实现
  select * from (select rownum as r，列名 ，列名 from 表名) temp where r>0 and r < 10;

• rowid

  -- rowid就是唯一标志记录物理位置的一个id,内容是当前行位于哪个文件，块，行的详细信息。
  select rowid,列名,列名  from 表名;

3.4 约束

在Oracle中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。

• 列级约束

  在定义列名和列类型后添加对列的约束，不为空且大于0 not null约束只能在列级上定义； price number(6,2) not null check(price>0),//列级约束

• 表级约束

  在列定义完成后在语句后加入约束语句，price大于0 constraint ck_price check(price>0),//表级约束

• chek 后也可跟选择字符 constraint ck_sex check(sex in ('男','女')),

约束分类

• not null（非空） 如果在列上定义了not null，那么当插入数据时，必须为列提供，数据不能为NULL。约束只能在列级定义，不能在表级定义。
• unique（唯一） 当定义了唯一约束后，该列值是不能重复的，但是可以为null。
• primary key（主键） 用于唯一的标识表行的数据，当定义主键约束后，该列不但不能重复而且不能为NULL。一张表最多只能有一个主键，但是可以由多个unique约束。 创建主键或唯一约束后，ORACLE会自动创建一个与约束同名的索引（UNIQUENES为UNIQUE唯一索引）。需要注意的是：每个表只能有且有一个主键约束。
• foreign key（外键） 用于定义主表和从表之间的关系，外键约束要定义在从表上，主要则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为NULL。 用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性. 外键约束是个有争议性的约束，它一方面能够维护数据库的数据一致性，数据的完整性。防止错误的垃圾数据入库； 另外一方面它会增加表插入、更新等SQL性能的额外开销，不少系统里面通过业务逻辑控制来取消外键约束。例如在数据仓库中，就推荐禁用外键约束。
• check 用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000～2000之间，如果不在1000～2000之间就会提示出错。

创建约束

-- not null
 create table t1(id number,name varchar2(20) constraint nn_t1_id not null);
 alter table t1 modify id constraint nn_t1_id not null;
 
 -- unique
create table t1(id number,qq number,constraint un_t1_qq unique(qq));
 alter table t1 add constraint un_t1_qq unique(qq);
 
 -- primary key
create table t1(id number,qq number,constraint pk_t1_id primary key(id));
alter table t1 add constraint pk_t1_id primary key(id);

-- foreign key
-- 当定义了外部键约束之后，要求外部键列的数据必须在主表的主键列（或惟一列）中存在，或者为NULL,FOREING KEY约束既可以在列级定义，也可以在表级定义。
create table t2(id number,cc number,constraint fk_t2_id foreign key(id) references t1(id));
alter table t1 add constraint pk_t2_id foreign key(id) references t1(id);

-- (1)  FOREING KEY:该选项用于指定在表级定义外部键约束。当在表级定义外部键约束时必须指定该选项，在列级定义外部键约束不需要指定该选项
--(2)  REFERENCES:该选项用于指定主表名及其主键列。当定义外部键约束时，该选项必须指定。
--(3)  ON DELETE CASCAED:该选项用于指定级联删除选项。如果在定义外部键约束时指定了该选项，那么当删除主表数据时会级联删除从表的相关数据。
--(4)  ON DELECT SET NULL:该选项用于指定转换相关的外部键值为NULL，如果在定义外部键约束时指定了该选项，那么当删除主表数据时会将从表外部键列的数据设置为NULL。


-- check
create table t3(id number,sal number,constraint ck_t3_sal check(sal between 5000 and 50000));
alter table t3 add constraint ck_t3_sal check(sal>5000);

约束维护

-- 增加约束
-- 如果增加UNIQUE、PRIMARY KEY、FOREIGN KEY 和CKECK 必须使用ALTER TABLE语句的ADD子句;
-- 如果增加NOT NULL约束，那么必须使用ALTER TABLE语句的MODIFY
ALTER TABLE table_name ADD [CONSTRAINT constraint_name]
constraint_type (column,...)
ALTER TABLE table_name MODIFY column
[CONSTRAINT constraint_name] NOT NULL;

-- 修改约束名
-- 在同一个方案中，约束名必须惟一,并且约束名也不能与其他对象同名
ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name
TO new_constraint_name;
-- 示例
ALTER TABLE emp01 RENAME CONSTRAINT SYS_C005028 TO ck_emp01_salary;

-- 禁止约束
-- 使约束临时失效。当禁止了约束之后，约束规则将不再生效
ALTER TABLE table_name
DISABLE CONSTRAINT constaint_name [CASCAED];--CASCAED用于指定级联禁止从表的外部键

-- 激活约束
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

-- 删除约束
ALTER TABLE table_name DROP
CONSTRAINT constraint_name |PRIMARY KEY

3.5 视图

视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图所对应的数据并不真正地存储在视图中，而是存储在所引用的数据表中，视图 的结构和数据是对数据表进行查询的结果。根据创建视图时给定的条件，视图可以是一个数据表的一部分，也可以是多个基表的联合，它存储了要执行检索的查询语句的定义，以便在引用该视图时使用。

使用视图的优点：

1.简化数据操作：视图可以简化用户处理数据的方式。

2.着重于特定数据：不必要的数据或敏感数据可以不出现在视图中。

3.视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限。

4.提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接口。

-- 创建或修改视图
-- OR REPLACE ：若所创建的试图已经存在，ORACLE 自动重建该视图；
-- FORCE ：不管基表是否存在 ORACLE 都会自动创建该视图；
-- subquery ：一条完整的 SELECT 语句，可以在该语句中定义别名；
-- WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；
-- WITH READ ONLY ：该视图上不能进行任何 DML 操作。只读视图
CREATE [OR REPLACE] [FORCE] VIEW view_name 
AS subquery 
[WITH CHECK OPTION ] 
[WITH READ ONLY]

-- 删除视图语法
DROP VIEW view_name

对于简单视图（单表并且没有聚合函数），不仅可以查询，还可以增删改。表中数据也会随之更改。

视图其实是一个虚拟的表，它的数据其实来自于表。如果更改了视图的数据，表的数据也自然会变化，更改了表的数据，视图也自然会变化。一个视图所存储的并不是数据，而是一条 SQL语句。

带有错误的视图

有的时候，创建视图时的表可能并不存在，但是以后可能会存在，需要添加force选项

create or replace FORCE view view_TEMP as select * from T_TEMP

复杂视图

视图的 SQL 语句中，有聚合函数或多表关联查询。

复杂视图中可以修改属于键保留表的列。

键保留表是理解连接视图修改限制的一个基本概念。该表的主键列全部显示在视图中,并且它们的值在视图中都是唯一且非空的。也就是说，表的键值在一个连 接视图中也是键值，那么就称这个表为键保留表

祝贺函数中没有键保留表，所以不能执行更新操作。

3.6 物化视图

视图是一个虚拟表（也可以认为是一条语句），基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次 访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化视图）。

物化视图与普通的视图相比的区别是物化视图是建立的副本，它类似于一张表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样的

-- BUILD IMMEDIATE 是在创建物化视图的时候就生成数据
-- BUILD DEFERRED 则在创建时不生成数据，以后根据需要再生成数据。默认为 BUILD IMMEDIATE。
-- 刷新（REFRESH）：指当基表发生了 DML 操作后，物化视图何时采用哪种方式和基表进行同步。
CREATE METERIALIZED VIEW view_name
[BUILD IMMEDIATE | BUILD DEFERRED ]
REFRESH [FAST|COMPLETE|FORCE] [
ON [COMMIT |DEMAND ] | START WITH (start_time) NEXT
(next_time)
]
AS
subquery

REFRESH 后跟着指定的刷新方法有三种：FAST、COMPLETE、FORCE。

FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。

COMPLETE 刷新对整个物化视图进行完全的刷新。

如果选择 FORCE 方式，则 Oracle 在刷新时会去判断是否可以进行快速刷新，

如果可以则采用 FAST 方式，否则采用 COMPLETE的方式。FORCE 是默认的方式。

刷新的模式有两种：ON DEMAND 和 ON COMMIT。

ON DEMAND 指需要手动刷新物化视图（默认）。ON COMMIT 指在基表发生 COMMIT 操作时自动刷新

手动刷新物化视图

-- 创建视图
create materialized view mv_address 
as
select ad.id,ad.name adname,ar.name ar_name 
from t_address ad,t_area ar 
where ad.areaid=ar.id

-- 刷新物化视图
begin
DBMS_MVIEW.refresh('MV_ADDRESS','C');
end;

EXEC DBMS_MVIEW.refresh('MV_ADDRESS','C');

自动刷新物化视图

-- 创建自动刷新物化视图 当 T_ADDRESS 表发生变化时，MV_ADDRESS2 自动跟着改变
create materialized view mv_address2 
refresh 
on commit
as
select ad.id,ad.name adname,ar.name ar_name 
from t_address ad,t_area ar 
where ad.areaid=ar.id

创建时不生成数据的物化视图

-- 创建物化视图
create materialized view mv_address3
build deferred 
refresh
on commit
as
select ad.id,ad.name adname,ar.name ar_name 
from t_address ad,t_area ar 
where ad.areaid=ar.id;

-- 生成数据 由于创建时指定的 on commit ,所以在修改数据后能立刻看到最新数据，无须再次执行 refresh
begin
DBMS_MVIEW.refresh('MV_ADDRESS3','C');
end;

创建增量刷新的物化视图

\1. 创建物化视图中涉及表的物化视图日志。

\2. 在查询语句中，必须包含所有表的 rowid ( 以 rowid 方式建立物化视图日志 )

SNAPTIME$$：用于表示刷新时间。
DMLTYPE$$：用于表示 DML 操作类型，I 表示 INSERT，D 表示 DELETE，U表示 UPDATE。 OLD_NEW$$：用于表示这个值是新值还是旧值。N（EW）表示新值，O（LD） 表示旧值，U 表示 UPDATE 操作。
CHANGE_VECTOR$$：表示修改矢量，用来表示被修改的是哪个或哪几个字段。此列是 RAW 类型，其实 Oracle 采用的方式就是用每个 BIT 位去映射一个列。插入操作显示为：FE, 删除显示为：OO 更新操作则根据更新字段的位置而显示不同的值

-- 首先创建物化视图日志
create materialized view log on t_address with rowid；
create materialized view log on t_area with rowid
-- 创建物化视图
create materialized view mv_address4
refresh fast
as
select ad.rowid adrowid ,ar.rowid arrowid, ad.id,ad.name 
adname,ar.name ar_name 
from t_address ad,t_area ar 
where ad.areaid=ar.id;

-- 手动刷新物化视图后，物化视图日志被清空，物化视图更新。
begin
DBMS_MVIEW.refresh('MV_ADDRESS4','C');
end;

3.7 同义词

同义词实质上是指定方案对象的一个别名。通过屏蔽对象的名称和所有者以及对分布式数据库的远程对象提供位置透明性，同义词可以提供一定程度的安全 性。同时，同义词的易用性较好，降低了数据库用户的 SQL 语句复杂度。同义词允许基对象重命名或者移动，这时，只需对同义词进行重定义，基于同义 词的应用程序可以继续运行而无需修改。

你可以创建公共同义词和私有同义词。其中，公共同义词属于 PUBLIC 特殊用户组，数据库的所有用户都能访问；而私有同义词包含在特定用户的方案中，只允许特定用户或者有基对象访问权限的用户进行访问。

同义词本身不涉及安全，当你赋予一个同义词对象权限时，你实质上是在给同义词的基对象赋予权限，同义词只是基对象的一个别名。

-- 创建同义词
--  synonym 表示要创建的同义词的名称，
--object 表示表，视图，序列等要创建同义词的对象的名称。
create [public] SYNONYM synooym for object;

-- 私有同义词
create synonym OWNERS for T_OWNERS;
-- 公有同义词 使用不同用户登录，也可以使用
create public synonym OWNERS2 for T_OWNERS;

3.8 索引

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次 数,从而提高数据访问性能。

索引是需要占据存储空间的，也可以理解为是一种特殊的数据。形式类似于下图的一棵“树”，而树的节点存储的就是每条记录的物理地址，也就是我们提到的伪列（ROWID）

-- 普通索引
create index 索引名称 on 表名(列名);
-- 示例
create index index_owners_name on T_OWNERS(name)

-- 唯一索引
create unique index 索引名称 on 表名(列名);
-- 示例
create unique index index_owners_watermeter on T_OWNERS(watermeter);

-- 复合索引
create index 索引名称 on 表名(列名,列名.....);
-- 示例
create index owners_index_ah on T_OWNERS(addressid,housenumber)

-- 反向键索引 
-- 当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。这样会增加查询的层数，性能会下降。
create index 索引名称 on 表名(列名) reverse;

-- 位图索引
-- 位图索引适合创建在低基数列上 
-- 位图索引不直接存储 ROWID，而是存储字节位到 ROWID 的映射
create bitmap index 索引名称 on 表名(列名);
-- 示例
create bitmap index index_owners_typeid on T_OWNERS(ownertypeid)

3.9 锁

锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

锁的分类

DML锁（data locks，数据锁），用于保护数据的完整性；

DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；

内部锁和闩（internal locks and latches），保护数据库的内部结构。

在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。

当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X 等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。

在数据行上只有X锁（排他锁）。在 Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行 DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。

表级锁

• 行级排他锁（Row Exclusive，简称RX锁）

  当我们进行DML时会自动在被更新的表上添加RX锁，或者也可以通过执行lock命令显式的在表上添加RX锁。在该锁定模式下，允许其他的事务通过DML语句修改相同表里的其他数据行，或通过lock命令对相同表添加RX锁定，但是不允许其他事务对相同的表添加排他锁（X锁）。

• 行级共享锁（Row Shared，简称RS锁）

  通常是通过select … from for update语句添加的，同时该方法也是我们用来手工锁定某些记录的主要方法。比如，当我们在查询某些记录的过程中，不希望其他用户对查询的记录进行更新操作，则可以发出这样的语句。当数据使用完毕以后，直接发出rollback命令将锁定解除。当表上添加了RS锁定以后，不允许其他事务对相同的表添加排他锁，但是允许其他的事务通过DML语句或lock命令锁定相同表里的其他数据行。

• 共享锁（Share，简称S锁）

  通过lock table in share mode命令添加该S锁。在该锁定模式下，不允许任何用户更新表。但是允许其他用户发出select …from for update命令对表添加RS锁。

• 排他锁（Exclusive，简称X锁）

  通过lock table in exclusive mode命令添加X锁。在该锁定模式下，其他用户不能对表进行任何的DML和DDL操作，该表上只能进行查询。

• 共享行级排他锁（Share Row Exclusive，简称SRX锁）

  通过lock table in share row exclusive mode命令添加SRX锁。该锁定模式比行级排他锁和共享锁的级别都要高，这时不能对相同的表进行DML操作，也不能添加共享锁。

这五种模式的TM锁的兼容关系如下表所示（√表示互相兼容的请求；×表示互相不兼容的请求；N/A表示没有锁定请求）：

-	S	X	RS	RX	SRX	N/A
S	√	×	√	×	×	√
X	×	×	×	×	×	√
RS	√	×	√	√	√	√
RX	×	×	√	√	×	√
SRX	×	×	√	×	×	√
N/A	√	√	√	√	√	√

从前面的描述中可以看到，我们不仅可以通过发出DML语句的方式，由Oracle自动在表级别上添加TM锁。我们还可以通过发出lock table命令主动地在表级别上添加TM锁，并在该命令中可以指定不同的锁定模式，其命令格式如下所示：

lock table in [row share][row exclusive]

[share][share row exclusive][exclusive] mode;

对Oracle数据库中的各SQL语句所产生的表级锁的情况进行汇总，如下表所示：

SQL语句	表锁定模	允许的表锁定模式
Select * from ……	RS	RS、RX、S、SRX、X
Insert into ……	RX	RS、RX
Update ……	RX	RS、RX
Delete from ……	RX	RS、RX
Select * from for update	RS	RS、RX、S、SRX
lock table in row share mode	RS	RS、RX、S、SRX
lock table in row exclusive mode	RX	RS、RX
lock table in share mode	S	RS、S
lock table in share row exclusive mode	SRX	RS
lock table in exclusive mode	X	RS

对于通过lock table命令主动添加的锁定来说，如果要释放它们，只需要发出rollback命令即可。

四、PL/SQL

概述：

• PL/SQL是Oracle在标准SQL语言上的过程性扩展
• 所有的PL/SQL程序都以块作为基本单位 块中包含过程化语句和SQL的DML语句。这些块可以按顺序出现，也可以相互嵌套（一个块在另一个块的内部）

4.1 块的分类

匿名块	匿名块是出现在应用程序中的没有名字且不存储到数据库中的块 匿名块出现在SQL语句可以出现的地方，它们可以调用其他程序，却不能被其他程序调用
命名块	命名块是一种带有标签的匿名块，标签为块指定了一个名称
子程序	子程序是存储在数据库中的过程（procedure）、函数（function），生成之后可以被多次执行
程序包	程序包是存储在数据库中的一组子程序、变量定义 程序包中的子程序可以被其他程序包或者子程序调用
触发器	触发器是一种存储在数据库中的命名块，生成之后可以被多次执行 在相应的触发器事件发生之前或之后就会被执行一次或多次

4.2 程序结构

-- 组成
-- declare 定义部分   定义部分用于定义常量、变量、游标、异常和复杂数据类型 
-- begin 执行部分  执行部分用于实现应用模块功能，该部分包含需要执行的PL/SQL语句和SQL语句 
-- exceotion 异常处理部分 异常处理部分用于处理执行部分可能出现的运行错误 PL/SQL块的基本结构
-- end；
--注意：DECLAREE、BEGIN和EXCEPTION后面没有“；”（分号），而END后则必须要带“；”（分号）。

-- 匿名块示例

set serveroutput on   --设置输出、显示环境变量
set verify off               --设置是否进行数据校验

DECLARE
 v_ename varchar2(50);   --定义快变量
 
BEGIN
	select ename into v_ename from emp where empno=&eno;	  --执行业务逻辑
	dbms_output.put_line('您要查找的姓名是：'|| v_ename);     
    
EXCEPTION
	WHEN NO_DATA_FOUND THEN                                            --异常处理部分
	dbms_output.put_line('输入的员工编号不存在！');                   --向控制台输出
	
END;

4.3 程序基础

注释

-- 单行注释可以在一行的任何地方由两个短横线（--）开始，并且直到该行的最后
-- 单行注释
-- 多行注释由“/”开始、“/”结束，可以跨越多行，但不允许嵌套
/
	多行注释
/

常量与变量

-- 在声明部分中可以声明需要使用的常量、变量、函数、游标、异常处理名称 
-- constant 常量声明的专用名词
-- ：= 赋值的符号
-- &no 提示用户输入，no为提示信息 还可通过SELECT INTO语句将从数据库表中查询的结果赋予变量 
select ename into v_ename from emp where empno=&eno;

数据类型

--当定义变量存放值时，变量应用合适的数据类型和长度，否则可能出现运行错误 为了避免错误，可以使用%TYPE属性来定义变量 

-- %type 当使用%TYPE属性定义变量时，Oracle会自动地按照数据库表列或其他变量来确定新变量的类型和长度
--定义和emp表的ename相同的类型
 v_ename emp.ename%type

-- %ROWTYPE如果一张表中包含较多的列，则可以使用%ROWTYPE来定义一个表示表中一行记录的变量 
--定义和dept相同的行类型
v_dept_row dept%rowtype

--record 类似高级语言中的结构 首先需要定义记录类型和记录变量 当引用记录成员时，必须将记录变量作为前缀  相当于java的class
-- 定义记录类型格式
type emp_record_type is RECORD(--定义一个记录类型
ename emp.ename%type,
total_sal sal%type
);
--声明记录类型变量
v_emp_record emp_record_type;
--调用存储在记录类型变量中的信息
 v_emp_record.ename
 
--table TABLE(索引表)相当于一个键值集合，键是唯一的，用于查找对应的值。键可以是整数或字符串           相当于java的集合
-- 定义table类型格式
type dept_table_type is table of dept%rowtype
index by binary_integer;
--声明table类型变量
 v_dept_table  dept_table_type;
--给变量赋值时有几行值 赋值几次  v_dept_table(n)   n就+1
--调用table类型变量中存储的信息
 v_dept_table(0).deptno

流程控制

• IF

  IF 条件表达式1 THEN
  语句段1
  ELSIF 条件表达式2 THEN
  语句段2
  ......
  ELSIF 条件表达式n
  语句段n
  END IF;

• CASE

  -- 当case后没有表达式时  when 后可跟条件表达式
  CASE  表达式
  WHEN 条件表达式结果1 THEN
  语句段1;
  WHEN 条件表达式结果2 THEN
  语句段2;
  ......
  ELSE
  语句段n;
  END CASE;

• 循环

  -- 普通循环
  LOOP
  语句段;
  EXIT [WHEN 条件表达式]
  END LOOP;
  
  -- when条件表达式可替换为
  --if  条件表达式 then  exit
  --end if；
  
  
  -- while循环
  WHILE 条件表达式 LOOP
  语句段;
  END LOOP;
  
  -- for循环  默认情况下每次循环，控制变量会自动增加1； 如果指定了reverse选项，则每次循环变量自动减少1。
  FOR 循环变量 in [REVERSE] 初值表达式..终值表达式 LOOP
  语句段;
  END LOOP;

• 异常处理

  --异常是指通过了编译的PL/SQL程序在运行时产生的错误
  --PL/SQL提供了一种异常错误处理机制，可以帮助实现对错误的捕获和处理
  -- 格式
  EXCEPTION
  WHEN 异常错误名称1 [OR 异常错误名称2......] THEN
  语句段1;
  WHEN异常错误名称3 [OR 异常错误名称4......] THEN
  语句段2;
  ......
  WHEN  OTHERS  THEN
  语句段3;
  
  --预定义异常
  --针对一些常见的错误，PL/SQL预定义了一些异常错误 当PL/SQL程序违反了Oracle的规定或超出了系统规定的限制时，就会隐含地引发一个预定义的错误 
  -- 自定义异常
  -- 在实际的PL/SQL程序开发过程中，为了具体的业务规则、编程和调试的需要，程序员可以自定义一些异常 
  --自定义异常必须要声明，并且必须使用RAISE语句显式地引发
  -- 在更新表中的数据时，如果没有符合条件的记录，则不会更新数据。因为这不是错误，所以不会有任何的错误提示，但可以使用自定义错误异常的方法来提示
  declare
  dept_no_emp exception;
  begin
  delete from emp where empno=&empno;
  if sql%notfound then
  raise dept_no_emp;
  else
  dbms_output.put_line('删除成功！');
  end if;
  exception 
  when dept_no_emp then
  dbms_output.PUT_LINE('该信息不存在！');    
  end;

4.4 子程序

子程序是指被命名的PL/SQL块，这种块可以带有参数，可以在不同应用中多次调用

PL/SQL有两种类型的子程序：过程和函数

过程用于执行特定操作，而函数则用于返回特定数据

创建子程序（过程）

-- 创建过程
--过程一般用于执行一个指定的操作，可以将常用的特定操作封装成过程
-- 格式
 CREATE [OR REPLACE] PROCEDURE procedure_name
(argument1 [mode1] datatype1, argument2 [mode2] datatype2, ...)  --参数名 参数模式  参数类型
IS [AS]  --声明部分
BEGIN --执行部分
EXCEPTION  --异常处理部分
END;
-- 执行过程
call pro_update_emp();

exec pro_update_emp;

begin
pro_update_emp;
end;

--参数
--创建过程时，无需为参数指定长度，因为过程中的参数长度最终是由传递给参数的外部数据长度来决定的
-- 带有in的参数
-- 当为过程定义参数时，如果不指定参数模式，则默认为输入参数
-- 实例
create or replace procedure 
pro_query_emp(v_no in emp.empno%type)  //声明输入参数
as
v_sal emp.sal%type;
begin  
select sal into v_sal from emp where empno=v_no;//使用输入参数
dbms_output.put_line('该员工薪水为：'||v_sal);
exception
 		 when no_data_found then
	dbms_output.put_line('找不到该员工！');
end;

-- 带有out的参数
-- 过程不仅可以用于执行特定操作，还可以用于输出数据
--在过程中输出数据时，需要使用OUT或IN OUT参数来完成 
-- 实例
create or replace procedure 
pro_query_emp(v_no in emp.empno%type, out_sal out number)  --声明输出参数
as
begin  
select sal into out_sal from emp where empno=v_no;  --给输出参数赋值
exception
 		 when no_data_found then
	dbms_output.put_line('找不到该员工！');
end;

-- 在过程子程序外，必须定义变量接收输出的数据
-- 带有 in out 参数
-- IN OUT参数也称为输入输出参数，当使用这种参数时，在调用过程之前需要通过变量给该种参数传递数据，调用结束后，Oracle会通过该变量将过程结果传递给应用

-- 为形参传递变量和数据可方式
---- 位置传递
---- 按位置传递是指在调用时按参数的排列顺序依次写出实参的名称，将形参与实参关联起来进行传递
----在这种方法中，形参与实参的名称是相互独立、没有关系的，次序才重要
---- 注意在oracle sql developer中报“无效SQL语句异常”。只能用call调用，而在命令窗口正常。

---- 名称传递
---- 名称传递是指在调用时按照形参与实参的名称写出实参所对应的形参，将形参与实参关联起来进行传递
----在这种方法中，形参与实参的名称是相互独立、没有关系的，名称的对应关系很重要，但次序不重要
----名称传递在调用子程序时指定参数名，并使用关联符号“=>”为其提供相应的数值或变量

----组合传递
----可以将按位置传递、按名称传递两种方法在同一调用中混合使用
但前面的实参必须使用按位置传递方法，而后面其余的实参则可以使用按名称传递的方法

创建子程序（函数）

-- 函数用于返回特定数据，如果在应用程序中经常需要通过执行SQL语句来返回特定数据，则可以基于这些操作创建特定的函数
-- 创建函数
-- 格式
CREATE [OR REPLACE] FUNCTION function_name
(argument1 [model] datatype1,
argument2 [mode2] datatype2,...)  --参数名  模式  类型
RETURN datatype
 IS|AS  -- 声明部分
BEGIN  --执行部分
 EXCEPTION   --异常处理部分
END;

-- 当创建函数时，通过使用输入参数，可以将应用的数据传递到函数中，最终通过执行函数可以将结果返回到应用程序中 
--当定义参数时，如果不指定参数模式，则默认为输入参数
------------ 其他参考过程开发-----------------

过程与函数对比

• 相同点：

  都使用IN模式的参数传入数据、OUT模式的参数返回数据 输入参数都可以接收默认值，都可以传值 调用时的实参都可以使用位置表示法或名称表示法 都有声明部分、执行部分和异常处理部分

• 各自适用场景

  一般而言，如果需要返回多个值或不返回值，就使用过程 如果只需要返回一个值，就使用函数 虽然函数带OUT模式的参数也能返回多个值，但是一般都认为这种方法属于不好的编程习惯或风格 过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值

子程序管理

--查看子程序的源码
--通过查询数据字典USER_SOURCE，可显示当前用户的所有子程序及其源代码
select text from user_source where name='PACK_UTIL';
-- 删除子程序
drop procedure proc_name;

4.5 包

概述

• 包（Package）用于组合逻辑相关的PL/SQL类型、PL/SQL项和PL/SQL子程序
• 通过使用PL/SQL包，不仅可以简化应用设计，提高应用性能，还可以实现信息隐藏、子程序重载等功能
• 包由包规范和包体两部分组成
• 当创建包时，需要首先创建包规范，然后再创建包体 包规范相当于接口 包体相当于接口的实现

创建包规范

• 概述

  • 包规范是包与应用程序之间的接口，用于定义包的公用组件，包括常量、变量、游标、过程和函数
  • 在包规范中所定义的公用组件不仅可以在包内引用，而且还可以由其他的子程序引用
  • 创建包规范时需要注意的是：为了实现信息隐藏，不应该将所有组件全部放在包规范处定义，而应该只定义公用组件

• 格式

  CREATE [OR REPLACE] PACKAGE package_name
  IS|AS
  public type and item declarations
  subprogram specifications
  END package_name;

创建包体

• 概述

  • 为了实现包规范中所定义的公用过程和函数，必须创建包体
  • 包体用于实现包规范所定义的过程和函数
  • 在创建包时，为了实现信息隐藏，应该在包体内定义私有组件

• 格式

  CREATE [OR REPLACE] PACKAGE  BODY package_name
  IS | AS
  private type and item declarations
  subprogram bodies
  END package_name;

• 注意事项

  • 在oracle sql developer中运行编译后，会出现错误，导致不能正常执行。是因为其bug，将最后的分号丢掉了，再编辑一下，加上即可。

调用包的组件

• 对于包的私有组件，只能在包内调用，并且可以直接调用

• 而对于包的公用组件，既可以在包内调用，又可以在其他应用中调用

• 在调用同一包内的其他组件时，可以直接调用，不需要添加包名作为前缀

• 当在其他应用中调用包的公用变量时，必须在公用变量、过程、函数名前添加包名作为前缀

• 调用方式

  • 包名.组件名

管理包

--查看包源代码
--通过查询数据字典USER_SOURCE，可以显示当前用户的包及其源代码
select  text  from  user_source  where  TYPE='PACKAGE' and name='PACK_UTIL';

-- 删除包
-- 只删除包体
DROP PACKAGE BODY
--同时删除包规范和包体
DROP PACKAGE

4.6 游标

概述：

• SQL语言是面向集合的，是对指定列的操作。如果要对列中的指定行进行操作，就必须使用游标。
• 当在PL/SQL块中执行查询语句（SELECT）和数据操纵语句（DML）时，Oracle会为其分配一个上下文区（Context Area）
• 游标是指向上下文区的指针，它为应用提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法

游标分类

-- 显式游标
--显式游标则用于处理SELECT语句返回的多行数据
--定义游标
CURSOR cursor_name IS select_statement;
--打开游标 
OPEN cursor_name;
--提取数据 
FETCH cursor_name INTO variable1,variable2,...;
--关闭游标 
CLOSE cursor_name;  
--属性
-- 显式游标属性用于返回显式游标的执行信息 
--游标属性使用格式为：游标名 + 属性名
%ISOPEN -- 用于确定游标是否已经打开。如果游标已经打开，则返回值为TRUE；否则返回FALSE
%FOUND -- 检查是否从结果集中提取到数据。如果提取到数据，则返回值为TRUE；否则返回FALSE 
%NOTFOUND --与%FOUND属性恰好相反，如果提取到数据，则返回值为FALSE；否则返回TRUE 
%ROWCOUNT --返回到当前行为止已经提取到的实际行数 
-- 示例
declare
v_dept dept%rowtype;    --还可使用%table属性
cursor dept_cursor is select * from dept where deptno>10;  --定义游标
begin
open dept_cursor;  --打开游标
loop
	fetch dept_cursor into v_dept;  --提取数据
	exit when dept_cursor%notfound;  --判断循环退出条件
 		 dbms_output.put_line('编号：'||v_dept.deptno ||'  名称：'||v_dept.dname||'  地址：'||v_dept.loc);
end loop;
close dept_cursor;  --关闭游标
end;


-- 参数游标
-- 带有参数的游标 在定义了参数游标之后，当使用了不同参数值多次打开游标时，可以生成不同的结果集
-- 格式
 CURSOR cursor_name(param_name datatype) IS select_statement;（select 语句）
-- 定义参数游标时，游标参数只能指定数据类型，而不能指定长度。
-- 游标for循环
-- 游标for循环简化游标处理 
--当使用游标for循环时，oracle会隐含地打开游标，提取数据并关闭游标。
--接受游标的类型的字段名与列名保持一致     不用关闭游标，报错
declare
cursor dept_cursor is select * from dept;
begin
for dept_row in dept_cursor loop
dbms_output.put_line('第'||dept_cursor%rowcount||'个部门：'||dept_row.dname);
end loop;
end;

-- 当使用游标for循环时，可以直接使用子查询。
begin
for dept_row in(select deptno,dname from dept) loop
dbms_output.put_line('第'||dept_dept.deptno||'个部门：'||dept_row.dname);
end loop;
end;

-- 使用游标变量
-- 当使用显式游标时，需要在定义部分指定其所对应的静态SELECT语句 
--而当使用游标变量时，开发人员可以在打开游标变量时指定其所对应的SELECT语句  
--语法
TYPE  ref_type_name  IS  REF  CURSOR;
cursor_variable  ref_type_name;


-- 隐含游标
-- 隐含游标用于处理SELECT INTO和DML语句 
--当执行一条DML语句或者SELECT...INTO语句时，都会创建一个隐含游标 
--隐含游标的名称是SQL，不能对SQL游标显式地执行OPEN、FETCH和CLOSE语句。
--Oracle隐式地打开、提取，并总是自动地关闭SQL游标 
--属性
%FOUND -- 判断隐含游标“sql"是否有影响的行数。
%notfound -- 与found相反

4.7 触发器

概述

• 触发器是指被隐含执行的存储过程，它可以使用PL/SQL进行开发 当发生特定事件（如修改表、创建对象、登录到数据库）时，Oracle会自动执行触发器的相应代码

类型

• DML触发器 ：在对数据库表进行DML操作时触发，并且可以对每行或者语句操作上进行触发。

• 替代触发器：是oracle8专门为进行视图操作的一种触发器

• 系统触发器 :对数据库系统事件进行触发，如启动、关闭等

触发器组成

• 触发事件 :DML或DDL语句

• 触发时间:是在触发事件发生之前(before)还是之后(after)触发

• 触发操作:使用PL/SQL块进行相应的数据库操作

• 触发对象:表、视图、模式、数据库

• 触发频率:触发器内定义的动作被执行的次数

• 触发器由触发事件、触发条件和触发操作3个部分组成

编写触发器执行代码的限制

• 触发器代码的大小不能超过32K。如果确实需要使用大量的代码创建触发器，则应该首先创建过程，然后在触发器中使用CALL语句调用过程
• 触发器代码只能包含SELECT、INSERT、UPDATE和DELETE语句， 不能包含DDL语句（CREATE、ALTER和DROP）和事务控制语句（COMMIT、ROLLBACK和SAVEPOINT）

创建DML触发器

--语句触发器 
-- 语句触发器是指当执行DML语句时被隐含执行的触发器 
--如果在表上针对某种DML操作创建了语句触发器，则当执行DML操作时会自动地执行触发器的相应代码 
--为了审计DML操作，或者确保DML操作安全执行时，可以使用语句触发器
-- 语法
CREATE [OR REPLACE] TRIGGER trigger_name
timing event1 [OR event2 OR event3]
ON table_name
 PL/SQL block;
 
-- trigger_name 	指定触发器名 timing  指定触发时机（BEFORE或AFTER） event 指定触发事件（INSERT、UPDATTE和DELETE） table_name 指定DML操作所对应的表名 

-- BEFORE语句触发器 
-- 如果指定了BEFORE关键字，则表示在执行DML操作之前触发触发器
-- 使用条件谓词 
-- 当在触发器中同时包含多个触发事件时，为了在触发器代码中区分具体的触发事件，可以使用以下3个条件谓词 
-- INSERTING：当触发事件是INSERT操作时，该条件谓词返回值为TRUE，否则返回FALSE
-- UPDATING：当触发事件是UPDATE操作时，该条件谓词返回值为TRUE，否则返回FALSE
-- DELETING：当触发事件是DELETE操作时，该条件谓词返回值为TRUE，否则返回FALSE

-- 行级触发器 
-- for each row
--:old  修改前的该行记录 insert为空null update更新以前的值 delete 删除以前的值
--:new  修改后的该行记录 insert的数据  update 更新后的值  delete 为空 null
create or replace trigger trig_update_sal
before update of sal,comm  --只对工资奖金有效
or delete on emp
for each row
when(old.deptno=30) 
begin
case 
when updating('sal') then
if :new.sal<:old.sal then
raise_application_error(-20002,'部门30的人员工资不能降');
end if;
when updating('comm') then
if :new.comm<:old.comm then
raise_application_error(-20001,'部门30的奖金不能降低！');
end if;
when deleting then
raise_application_error(-20003,'不能删除部门30的员工');
end case;
end;

-- 创建AFTER语句触发器  
-- 如果指定了AFTER关键字，则表示在执行DML操作之后触发触发器
--利用行触发器实现级联更新。在修改了主表dept中的deptno之后（AFTER），级联的、自动的更新子表emp中原来在该部门的deptno。 
-- 在触发器中调用存储过程  
-- 有时业务逻辑过于复杂，触发器内容有限（32K)，只能借助于存储过程
--在删除dept表中记录时，将原有的记录保存到一个回收表delDept中

创建INSTEAD OF 触发器

• 概述：为了在不能执行DML操作的复杂视图上执行DML操作，必须基于视图创建INSTEAD OF触发器

• 注意事项

  • INSTEAD OF选项只适用于视图
  • 当基于视图创建触发器时，不能指定BEFORE和AFTER选项
  • 当创建INSTEAD OF触发器时，必须指定FOR EACH ROW选项
  • 只能在视图上创建INSTEAD OF触发器，而不能创建其他类型的触发器

• 创建查询视图

  create or replace view emp_view as
  select deptno,count(*) total_employeer,sum(sal) total_sal
  from emp group by deptno;

• 创建instead_of触发器

  create or replace trigger emp_view_del
  instead of delete on emp_view for each row
  begin
  delete from emp where deptno=:old.deptno;
  end;

创建系统事件触发器

• 概述

  • 系统事件触发器是指基于Oracle系统事件所创建的触发器

    --创建系统事件触发器时，应用开发人员经常需要使用事件属性函数
    ora_client_ip_address		--返回客户端的IP地址
    ora_database_name			--返回当前数据库名
    ora_dict_obj_name			--返回DDL操作所对应的数据库对象名
    ora_dict_obj_owner			--返回DDL操作所对应的对象的所有者名
    ora_dict_obj_type			--返回DDL操作对应的数据库对象的类型
    ora_instance_num			--返回例程号
    ora_is_alter_column(column_name IN VARCHAR2)		--检测特定列是否被修改
    ora_is_drop_column(column_name IN VARCHAR2)			--检测特定列是否被删除
    ora_login_user										--返回登录用户名
    ora_sysevent										--返回触发触发器的系统事件名

• 创建例程启动和关闭触发器

  • 为了跟踪例程启动和关闭事件，可以分别创建例程启动触发器和例程关闭触发器

  • 创建事件表

    create table event_table(event_name varchar2(30),event_time date)

  • 创建例程启动和关闭触发器

    --打开或关闭数据库时执行对应触发器的相应代码
    create or replace trigger tri_shutdown
    before shutdown on database
    begin
    insert into event_table values(ora_sysevent,sysdate);
    end;
    
    create or replace trigger tri_startup
    after startup on database
    begin
    insert into event_table values(ora_sysevent,sysdate);
    end;

• 创建登录和退出触发器

  • 为了记载用户的登录和退出事件，可以分别创建登录和退出触发器

  • 创建用于存放登录和退出信息的表

    create table log_event(username varchar2(20),ipAddress varchar2(20),logonTime timestamp,logoffTime timestamp);

  • 创建登录和退出触发器

    --用户登录数据库或断开与数据库的连接时执行相应的触发器代码
     create or replace trigger logon_trigger
    after logon on database
    begin
    insert into log_event(username,ipaddress,logonTime)
    values(ora_login_user,ora_client_ip_address,sysdate);
    end;
    
    create or replace trigger logoff_trigger
    after logoff on database
    begin
    insert into log_event(username,ipaddress,logoffTime)
    values(ora_login_user,ora_client_ip_address,sysdate);
    end;

管理触发器

• 显示触发器信息

  • 通过查询数据字典视图USER_TRIGGERS，可以显示当前用户所包含的所有触发器信息

• 禁用或启用触发器

• 重新编译触发器

  • 当使用ALTER TABLE命令修改表的结构时，会使触发器变为无效状态 为了使触发器继续生效，需要重新编译触发器

• 删除触发器

五、事务

事务参考数据库相关.md文档

5.1 事务控制语句

--在oracle中,用户不可以显示使用命令来开始一个事务
--oracle任务第一条修改数据库的语句,或者一些要求事务处理的场合都是事务的隐式开始
--IMMEDIATE选项的功能与ON相同
--n表示当Oracle数据库成功执行了n条insert,update,delete或PL/SQL程序块时自动提交事务
set autocommit{on| off |immediate | n}

-- 事务关键词
-- Commit 提交事务 对数据库的操作做持久的保存
-- Rollback 回滚事务 取消对数据库所作的任何操作
-- Savepoint 在事务中建立一个存储的点
-- Rollback to <回滚点>

-- 查看事务
-- 若事务是匿名事务，则name字段值为空
select name,status,addr,start_time from v$transaction;

--命名事务
 begin
set transaction name 'deptTran';   --命名事务，之前的事务必须提交或回滚
insert into scott.dept values(35,'工程部','天津');
end;

select name,status,addr,start_time from v$transaction;

六、索引sql优化

参考数据库相关.md文档

七、安装

PL SQL使用

安装后选择tools-》preferences——》配置oci信息——》在Oracle的安装目录下找到tnsnames.ora文件，拷贝到特定目录，修改地址等信息——》设置TNS_ADMIN变量执行目录